package smartcrud.spring.models.cms;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import smartcrud.common.orm.Page;
import smartcrud.common.orm.PropertyFilter;
import smartcrud.common.orm.jdbc.Sql;

@Repository
public class AttachmentDaoJdbcImpl implements AttachmentDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public Attachment createAttachment(final Attachment o) {
		final StringBuffer sql = new StringBuffer(
				"insert into CMS_ATTACHMENT(FILE_TYPE,ORIGINAL_NAME,URL,SIZE,POST_ID,DOWNLOAD_COUNT,CREATE_DATE)");
		sql.append(" values(?,?,?,?,?,?,now())");

		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(/* sql, */new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql.toString(),
						Statement.RETURN_GENERATED_KEYS);
				int z = 0;
				ps.setString(++z, o.getFileType());
				ps.setString(++z, o.getOriginalName());
				ps.setString(++z, o.getUrl());
				ps.setObject(++z, o.getSize());
				ps.setObject(++z, o.getPostId());
				ps.setObject(++z, o.getDownloadCount());
				return ps;
			}
		}, keyHolder);
		o.setId(keyHolder.getKey().intValue());
		return o;
	}

	@Override
	public void updateAttachmentsWithPostId(List<Attachment> attchments,
			Long postId) {
		List<Object[]> batchArgs = new ArrayList<Object[]>();
		if (attchments != null) {
			for (Attachment attachment : attchments) {
				Object[] args = new Object[] { postId, attachment.getId() };
				batchArgs.add(args);
			}
		}

		String sql = "update cms_attachment set post_id = ? where id=?";
		jdbcTemplate.batchUpdate(sql, batchArgs);
	}

	@Override
	public void deleteAttachment(Long id) {
		jdbcTemplate.update("delete from CMS_ATTACHMENT where ID=?", id);
	}
	
	@Override
	public void deleteAttachmentsByPostId(Long id) {
		jdbcTemplate.update("delete from CMS_ATTACHMENT where POST_ID=?", id);
	}

	@Override
	public void updateAttachment(Attachment o) {
		final StringBuffer sql = new StringBuffer(
				"update CMS_ATTACHMENT set FILE_TYPE=?,URL=?,");
		sql.append(" where ID=?");
		jdbcTemplate.update(sql.toString(), o.getFileType(), o.getUrl(),
				o.getId());
	}

	@Override
	public Attachment findAttachmentById(Long id) {
		final String sql = "select ID,FILE_TYPE,ORIGINAL_NAME,URL,SIZE,POST_ID,DOWNLOAD_COUNT,CREATE_DATE from CMS_ATTACHMENT where ID=?";
		return jdbcTemplate.queryForObject(sql, new RowMapper<Attachment>() {
			@Override
			public Attachment mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				Attachment o = new Attachment();
				o.setId(rs.getInt("ID"));
				o.setFileType(rs.getString("FILE_TYPE"));
				o.setOriginalName(rs.getString("ORIGINAL_NAME"));
				o.setUrl(rs.getString("URL"));
				o.setSize(rs.getInt("SIZE"));
				o.setPostId(rs.getInt("POST_ID"));
				o.setDownloadCount(rs.getInt("DOWNLOAD_COUNT"));
				o.setCreateDate(rs.getDate("CREATE_DATE"));
				return o;
			}
		}, id);
	}

	@Override
	public List<Attachment> findAllAttachments() {
		final String sql = "select ID,FILE_TYPE,ORIGINAL_NAME,URL,SIZE,POST_ID,DOWNLOAD_COUNT,CREATE_DATE from CMS_ATTACHMENT";
		return jdbcTemplate.query(sql, new RowMapper<Attachment>() {
			@Override
			public Attachment mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				Attachment o = new Attachment();
				o.setId(rs.getInt("ID"));
				o.setFileType(rs.getString("FILE_TYPE"));
				o.setOriginalName(rs.getString("ORIGINAL_NAME"));
				o.setUrl(rs.getString("URL"));
				o.setSize(rs.getInt("SIZE"));
				o.setPostId(rs.getInt("POST_ID"));
				o.setDownloadCount(rs.getInt("DOWNLOAD_COUNT"));
				o.setCreateDate(rs.getDate("CREATE_DATE"));
				return o;
			}
		});
	}

	@Override
	public int count() {
		return jdbcTemplate.queryForInt("select count(1) from CMS_ATTACHMENT");
	}

	@Override
	public int count(Page<Attachment> page, final List<PropertyFilter> filters) {
		Sql sql = new Sql("select count(1) from CMS_ATTACHMENT where 1=1 ",
				page, filters);
		sql.build4count();
		return jdbcTemplate.queryForInt(sql.getSqlBody(), sql.getSqlParams());
	}

	@Override
	public Page<Attachment> findByPage(Page<Attachment> page,
			final List<PropertyFilter> filters) {
		if (page.isAutoCount()) {
			int totalCount = count(page, filters);
			page.setTotalCount(totalCount);
		}

		Sql sql = new Sql("select * from CMS_ATTACHMENT where 1=1 ", page,
				filters);
		sql.build();

		List<Attachment> content = jdbcTemplate.query(sql.getSqlBody(),
				new RowMapper<Attachment>() {
					@Override
					public Attachment mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Attachment o = new Attachment();
						o.setId(rs.getInt("ID"));
						o.setFileType(rs.getString("FILE_TYPE"));
						o.setOriginalName(rs.getString("ORIGINAL_NAME"));
						o.setUrl(rs.getString("URL"));
						o.setSize(rs.getInt("SIZE"));
						o.setPostId(rs.getInt("POST_ID"));
						o.setDownloadCount(rs.getInt("DOWNLOAD_COUNT"));
						o.setCreateDate(rs.getDate("CREATE_DATE"));
						return o;
					}
				}, sql.getSqlParams());

		page.setContent(content);
		return page;
	}

	@Override
	public List<Attachment> findAttachmentsByPostId(Long postId) {
		final String sql = "select ID,FILE_TYPE,ORIGINAL_NAME,URL,SIZE,POST_ID,DOWNLOAD_COUNT,CREATE_DATE from CMS_ATTACHMENT where POST_ID=?";
		return jdbcTemplate.query(sql, new RowMapper<Attachment>() {
			@Override
			public Attachment mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				Attachment o = new Attachment();
				o.setId(rs.getInt("ID"));
				o.setFileType(rs.getString("FILE_TYPE"));
				o.setOriginalName(rs.getString("ORIGINAL_NAME"));
				o.setUrl(rs.getString("URL"));
				o.setSize(rs.getInt("SIZE"));
				o.setPostId(rs.getInt("POST_ID"));
				o.setDownloadCount(rs.getInt("DOWNLOAD_COUNT"));
				o.setCreateDate(rs.getDate("CREATE_DATE"));
				return o;
			}
		}, postId);
	}
}
